package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;
import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.compiere.model.MAccount;
import org.compiere.model.MAsset;
import org.compiere.model.MAssetTransaction;
import org.compiere.model.MAssetTransactionLine;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MInvoice;
import org.compiere.model.MOrg;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.MHRJob;
import extend.org.compiere.utils.Util;

public class A_Asset_Form_DA1 extends SvrProcess {
	
	/*Current context*/
	private Properties m_ctx;
	
	private ProcessInfo pi;
	
	Integer p_C_ApprovalDeliverer_ID, p_C_ApprovalRecipient_ID, p_Record_ID, p_Manager_ID,
	p_chiefComm, p_memberComm1, p_memberComm2, p_memberComm3, p_memberComm4, p_memberComm5, 
	p_C_Recipient_ID, p_C_AccountantRecipient_ID;
	
	Timestamp p_DateDeliverer, p_DateRecipient, p_DateReceive, p_DateDelivery;
	
	String ResultPath, p_C_Deliverer, p_C_JobDeliverer, p_C_AccountantDeliverer;
	
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null) {
				;
			} else if (name.equals("DocumentNo")) {
				p_Record_ID = para[i].getParameterAsInt();
			} else if (name.equals("C_ApprovalDeliverer_ID")) {
				p_C_ApprovalDeliverer_ID = para[i].getParameterAsInt();
			} else if (name.equals("C_ApprovalRecipient_ID")) {
				p_C_ApprovalRecipient_ID = para[i].getParameterAsInt();
			} else if (name.equals("DateDeliverer")) {
				p_DateDeliverer = (Timestamp) para[i].getParameter();
			} else if (name.equals("DateRecipient")) {
				p_DateRecipient = (Timestamp) para[i].getParameter();
			} else if (name.equals("chiefComm")) {
				p_chiefComm = para[i].getParameterAsInt();
			} else if (name.equals("memberComm1")) {
				p_memberComm1 = para[i].getParameterAsInt();
			} else if (name.equals("memberComm2")) {
				p_memberComm2 = para[i].getParameterAsInt();
			} else if (name.equals("memberComm3")) {
				p_memberComm3 = para[i].getParameterAsInt();
			} else if (name.equals("memberComm4")) {
				p_memberComm4 = para[i].getParameterAsInt();
			} else if (name.equals("memberComm5")) {
				p_memberComm5 = para[i].getParameterAsInt();
			} else if (name.equals("C_Deliverer")) {
				p_C_Deliverer = (String) para[i].getParameter();
			} else if (name.equals("C_JobDeliverer")) {
				p_C_JobDeliverer = (String) para[i].getParameter();
			} else if (name.equals("DateDelivery")) {
				p_DateDelivery = (Timestamp) para[i].getParameter();
			} else if (name.equals("C_Recipient_ID")) {
				p_C_Recipient_ID = para[i].getParameterAsInt();
			} else if (name.equals("DateReceive")) {
				p_DateReceive = (Timestamp) para[i].getParameter();
			} else if (name.equals("C_AccountantDeliverer")) {
				p_C_AccountantDeliverer = (String) para[i].getParameter();
			} else if (name.equals("C_AccountantRecipient_ID")) {
				p_C_AccountantRecipient_ID = para[i].getParameterAsInt();
			} else 
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
	}

	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_A1_";
		Integer pathI = 0;
		boolean flag = true;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xlsx");
			try {
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(";
					ResultPath = ResultPath.substring(0, 9) + (++pathI) + ")";
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" + (++pathI) + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xlsx";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate"));
		
		Workbook wb = new XSSFWorkbook(entry.getInputStream());
		Sheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		//Cell Styling
		CellStyle mergeStyle = wb.createCellStyle();
		mergeStyle.setBorderBottom(( short)1);
		mergeStyle.setBorderTop(( short)1);
		mergeStyle.setBorderLeft(( short)1);
		mergeStyle.setBorderRight(( short)1);
		Font mergeFont = wb.getFontAt(sheet.getRow(9).getCell(3).getCellStyle().getFontIndex());
		mergeStyle.setFont(mergeFont);
		mergeStyle.setWrapText( true);
		mergeStyle.setVerticalAlignment(CellStyle. VERTICAL_CENTER);

		//Document's Static data
		MAssetTransaction transact = new MAssetTransaction(m_ctx, p_Record_ID, get_TrxName());
		MInvoice invoice = new MInvoice(m_ctx, transact.getC_Invoice_ID(), get_TrxName());
		//Deliverer
		sheet.getRow(10).getCell(26).setCellValue((new MBPartner(m_ctx, invoice.getC_BPartner_ID(), get_TrxName()).getName()));
		sheet.getRow(9).getCell(57).setCellValue((new MBPartner(m_ctx, invoice.getC_BPartner_ID(), get_TrxName()).getFI_BIN_IIN()));
		//Recipient
		sheet.getRow(12).getCell(26).setCellValue(MOrg. get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(11).getCell(57).setCellValue((new MBPartner(m_ctx , MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx )).getLinkedC_BPartner_ID(get_TrxName()), get_TrxName()).getFI_BIN_IIN()));
		
		//Approval Deliverer
		String sqljob = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_C_ApprovalDeliverer_ID;
		int idjob = DB.getSQLValue(get_TrxName(), sqljob);
		sheet.getRow(19).getCell(0).setCellValue((new MHRJob(m_ctx, idjob, get_TrxName()).getName()));
		sheet.getRow(19).getCell(17).setCellValue((new MBPartner(m_ctx, p_C_ApprovalDeliverer_ID, get_TrxName()).getName()));
		sheet.getRow(21).getCell(9).setCellValue((new SimpleDateFormat("dd").format(p_DateDeliverer)));
		sheet.getRow(21).getCell(12).setCellValue((new SimpleDateFormat("MMMMM").format(p_DateDeliverer)));
		sheet.getRow(21).getCell(18).setCellValue((new SimpleDateFormat("yyyy").format(p_DateDeliverer)));
		//Approval Recipient
		sqljob = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_C_ApprovalRecipient_ID;
		idjob = DB.getSQLValue(get_TrxName(), sqljob);
		sheet.getRow(19).getCell(36).setCellValue((new MHRJob(m_ctx, idjob, get_TrxName()).getName()));
		sheet.getRow(19).getCell(53).setCellValue((new MBPartner(m_ctx, p_C_ApprovalRecipient_ID, get_TrxName()).getName()));
		sheet.getRow(21).getCell(45).setCellValue((new SimpleDateFormat("dd").format(p_DateRecipient)));
		sheet.getRow(21).getCell(48).setCellValue((new SimpleDateFormat("MMMMM").format(p_DateRecipient)));
		sheet.getRow(21).getCell(54).setCellValue((new SimpleDateFormat("yyyy").format(p_DateRecipient)));
		
		//Document
		sheet.getRow(25).getCell(43).setCellValue(transact.getDocumentNo());
		sheet.getRow(25).getCell(51).setCellValue(transact.getDateAcct());
		
		//First static table
		//Deliverer
		sheet.getRow(33).getCell(0).setCellValue((new MBPartner(m_ctx, invoice.getC_BPartner_ID(), get_TrxName()).getName()));
		//Recipient
		sheet.getRow(33).getCell(15).setCellValue(MOrg. get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		//Base
		//Contract 
//		sheet.getRow(31).getCell(39).setCellValue(invoice.getCMS_Contract().getCMS_Contract_ContractNum());
//		sheet.getRow(31).getCell(46).setCellValue((new SimpleDateFormat("dd").format(invoice.getCMS_Contract().getCMS_Contract_CreatDate())));
//		sheet.getRow(31).getCell(49).setCellValue((new SimpleDateFormat("MMMMM").format(invoice.getCMS_Contract().getCMS_Contract_CreatDate())));
//		sheet.getRow(31).getCell(55).setCellValue((new SimpleDateFormat("yyyy").format(invoice.getCMS_Contract().getCMS_Contract_CreatDate())));
		
		//MOL and job deliverer
		
		//Commissions
		sqljob = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_chiefComm;
		idjob = DB.getSQLValue(get_TrxName(), sqljob);
		sheet.getRow(67).getCell(10).setCellValue((new MHRJob(m_ctx, idjob, get_TrxName()).getName()));
		sheet.getRow(67).getCell(29).setCellValue((new MBPartner(m_ctx, p_chiefComm, get_TrxName()).getName()));

		sqljob = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm1;
		idjob = DB.getSQLValue(get_TrxName(), sqljob);
		sheet.getRow(69).getCell(8).setCellValue((new MHRJob(m_ctx, idjob, get_TrxName()).getName()));
		sheet.getRow(69).getCell(27).setCellValue((new MBPartner(m_ctx, p_memberComm1, get_TrxName()).getName()));
		sqljob = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm2;
		idjob = DB.getSQLValue(get_TrxName(), sqljob);
		sheet.getRow(71).getCell(8).setCellValue((new MHRJob(m_ctx, idjob, get_TrxName()).getName()));
		sheet.getRow(71).getCell(27).setCellValue((new MBPartner(m_ctx, p_memberComm2, get_TrxName()).getName()));
		sqljob = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm3;
		idjob = DB.getSQLValue(get_TrxName(), sqljob);
		sheet.getRow(73).getCell(8).setCellValue((p_memberComm3 == null ? "" : new MHRJob(m_ctx, idjob, get_TrxName()).getName()));
		sheet.getRow(73).getCell(27).setCellValue((p_memberComm3 == null ? "" : new MBPartner(m_ctx, p_memberComm3, get_TrxName()).getName()));  
		sqljob = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm4;
		idjob = DB.getSQLValue(get_TrxName(), sqljob);
		sheet.getRow(75).getCell(8).setCellValue((p_memberComm4 == null ? "" : new MHRJob(m_ctx, idjob, get_TrxName()).getName()));
		sheet.getRow(75).getCell(27).setCellValue((p_memberComm4 == null ? "" : new MBPartner(m_ctx, p_memberComm4, get_TrxName()).getName()));
		sqljob = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_memberComm5;
		idjob = DB.getSQLValue(get_TrxName(), sqljob);
		sheet.getRow(77).getCell(8).setCellValue((p_memberComm5 == null ? "" : new MHRJob(m_ctx, idjob, get_TrxName()).getName()));
		sheet.getRow(77).getCell(27).setCellValue((p_memberComm5 == null ? "" : new MBPartner(m_ctx, p_memberComm5, get_TrxName()).getName()));
		
		//Deliverer
		sheet.getRow(86).getCell(2).setCellValue(p_C_JobDeliverer);
		sheet.getRow(86).getCell(19).setCellValue(p_C_Deliverer);
		sheet.getRow(88).getCell(1).setCellValue((new SimpleDateFormat("dd").format(p_DateDelivery)));
		sheet.getRow(88).getCell(4).setCellValue((new SimpleDateFormat("MMMMM").format(p_DateDelivery)));
		sheet.getRow(88).getCell(10).setCellValue((new SimpleDateFormat("yyyy").format(p_DateDelivery)));
		
		//Recipient
		sqljob = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_C_Recipient_ID;
		idjob = DB.getSQLValue(get_TrxName(), sqljob);
		sheet.getRow(86).getCell(36).setCellValue((idjob == 0 ? "" : new MHRJob(m_ctx, idjob, get_TrxName()).getName()));
		sheet.getRow(86).getCell(54).setCellValue(new MBPartner(m_ctx, p_C_Recipient_ID, get_TrxName()).getName());
		sheet.getRow(88).getCell(34).setCellValue((new SimpleDateFormat("dd").format(p_DateReceive)));
		sheet.getRow(88).getCell(37).setCellValue((new SimpleDateFormat("MMMMM").format(p_DateReceive)));
		sheet.getRow(88).getCell(43).setCellValue((new SimpleDateFormat("yyyy").format(p_DateReceive)));
		
		//Accountants
		sheet.getRow(100).getCell(16).setCellValue(p_C_AccountantDeliverer);
		sheet.getRow(100).getCell(49).setCellValue(new MBPartner(m_ctx, p_C_AccountantRecipient_ID, get_TrxName()).getName());
	
		//Second Table
		int row = 45;
		
		for (MAssetTransactionLine tLine: transact.getLines(false)) {
			MAsset asset = new MAsset(m_ctx, tLine.getA_Asset_ID(), get_TrxName());
			
			//Shifting and Merging
			sheet.shiftRows(row, row + 62, 1);
			sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 6));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 7, 13));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 14, 19));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 20, 25));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 26, 31));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 32, 37));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 38, 42));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 43, 46));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 47, 50));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 51, 59));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 60, 64));
			
			R = sheet.createRow(row);
			for(int i = 0; i <= 64; i++){
				C = R.createCell(i);
				C.setCellStyle(mergeStyle);
			}
			
			sheet.getRow(row).getCell(0).setCellValue(asset.getName());
			sheet.getRow(row).getCell(7).setCellValue(asset.getAssetCost(p_DateReceive).doubleValue());
			sheet.getRow(row).getCell(14).setCellValue(asset.getUseLifeMonths());
			sheet.getRow(row).getCell(20).setCellValue(asset.getAccumDepreciation(p_DateReceive).doubleValue());
			sheet.getRow(row).getCell(26).setCellValue(asset.getBalanceCost(p_DateReceive).doubleValue());
			sheet.getRow(row).getCell(32).setCellValue(asset.getValue());
//			sheet.getRow(row).getCell(38).setCellValue();
			sheet.getRow(row).getCell(43).setCellValue((new MAccount(m_ctx, asset.getAcctValue("A_AccumDepreciation_Acct"), get_TrxName()).getCombination()));	
			sheet.getRow(row).getCell(47).setCellValue((new MAccount(m_ctx, asset.getAcctValue("A_Asset_Acct"), get_TrxName()).getCombination()));
			sheet.getRow(row).getCell(51).setCellValue((new SimpleDateFormat("dd.MM.yyyy").format(asset.getA_Asset_CreateDate())));
//			sheet.getRow(row).getCell(60).setCellValue();
			
			row++;
		}
		
		//Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		
		//Open the file with the application associated
		Util.launchFile(new File(ResultPath));
		
		return Msg.translate(m_ctx, "Success");
	}

}
